﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace ESAM_Assignment_3
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            #region Check if the database exist | This segment of codes is NOT to be modified
            CheckDatabase();
            #endregion
        }

        #region Set of methods to handle creation and deletion of database | This segment of codes is NOT to be modified
        /// <summary>
        /// Method to create the database and tables. Test data will be also be inserted into the tables as well.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnGo_Click(object sender, EventArgs e)
        {
            string message = "";
            message += "Creating database.... <br>" + DBManager.CreateDatabase();
            message += "<br><br>Creating USERS table.... <br>" + DBManager.CreateUsersTable();
            message += "<br><br>Creating COMMENTS table.... <br>" + DBManager.CreateCommentsTable();
            message += "<br><br>Creating Relationships between tables.... <br>" + DBManager.CreateRelationship();

            lblStatus.Text = message;
            CheckDatabase();
        }

        /// <summary>
        /// Button event to delete the entire database.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnRemove_Click(object sender, EventArgs e)
        {
            string message = "";
            message += "Deleting database.... <br>" + DBManager.DropDatabase();

            lblStatus.Text = message;
            CheckDatabase();
        }

        /// <summary>
        /// Method to check the status of the database. If the database has not been created yet, inform the user to first create the database.
        /// Do NOT modify this method.
        /// </summary>
        private void CheckDatabase()
        {
            Button btnLogin = (Button)Master.FindControl("btnLogin");

            if (DBManager.IsDatabaseExist())
            {
                lblCreate.Text = "Database has already been created.";
                lblCreate.ForeColor = System.Drawing.Color.Blue;
                btnGo.Enabled = false;
                btnRemove.Enabled = true;
                btnLogin.Enabled = true;
                btnUpdate.Enabled = true; // added line of code to make updating DB available
            }
            else
            {
                lblCreate.Text = "Database has NOT been created yet. Please create it before proceeding further.";
                lblCreate.ForeColor = System.Drawing.Color.Red;
                btnGo.Enabled = true;
                btnRemove.Enabled = false;
                btnLogin.Enabled = false;
                btnUpdate.Enabled = false;
            }
        }
        #endregion

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the USERS table in the newly created database
            try
            {
                myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ESAM_Assignment"].ConnectionString);
                myConn.Open();

                strQuery = "INSERT INTO Users (Username, Fullname, Password) VALUES ('admin', 'Admin', 'admin')";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.ExecuteNonQuery();

                List<int> listUserID = new List<int>();
                List<string> listPassword = new List<string>();

                strQuery = "SELECT UserID, Password FROM Users";
                myCmd = new SqlCommand(strQuery, myConn);
                SqlDataReader dr = myCmd.ExecuteReader();
                while (dr.Read())
                {
                    int userID = dr.GetInt32(0); // userID
                    string password = dr.GetString(1); // password
                    listUserID.Add(userID);
                    listPassword.Add(password);
                }
                dr.Close();

                for (int i = 0; i < listUserID.Count(); i++)
                {
                    strQuery = "UPDATE Users SET Password=@HashedPassword WHERE UserID=" + listUserID[i];
                    myCmd = new SqlCommand(strQuery, myConn);
                    myCmd.Parameters.AddWithValue("@HashedPassword", UserLogin.Hash(listPassword[i]));
                    myCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConn.Close();
            }
        }
    }
}